-- @owner: opentestcase023
-- @date: 2022/02/24
-- @testpoint: 在从src视图删除元素时删除des表中对应行 each row 删除src表中id1最小行 each statement

--step1:建表;expect:成功
drop table if exists t_trigger_src cascade;
drop table if exists t_trigger_des cascade;
create table t_trigger_src(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:在从src视图删除元素时删除des表中对应行 each row 删除src表中id1最小行 each statement;expect:成功
create or replace view t_trigger_src_view1 as select * from t_trigger_src;
create or replace function tri_delete_func() returns trigger as
$$
declare
begin
delete from t_trigger_des where id1 in(select 1 from t_trigger_src order by id1 limit 1);
return old;
end
$$ language plpgsql;
/
drop trigger if exists delete_trigger on t_trigger_src_view1;
create trigger delete_trigger before delete on t_trigger_src_view1
for each statement
execute procedure tri_delete_func();
/
insert into t_trigger_src values(1,1,1),(2,2,2);
insert into t_trigger_src values(3,3,3),(4,4,4);
insert into t_trigger_des values(1,1,1),(2,2,2);
insert into t_trigger_des values(3,3,3),(4,4,4);
select * from t_trigger_des order by id1;
create or replace function delete_func()
returns trigger as $$
begin
delete from  t_trigger_src where id1=old.id1;
return new;
end;
$$language plpgsql;
/
drop trigger if exists insert_trigger_1 on t_trigger_src_view1;
create trigger insert_trigger_1
instead of delete on t_trigger_src_view1
for each row
execute procedure delete_func();
/
delete from t_trigger_src_view1 where id1=3;
select * from t_trigger_src order by id1;
select * from t_trigger_des order by id1;

--step3:清理环境;expect:成功
drop table if exists t_trigger_src cascade;
drop table if exists t_trigger_des cascade;